﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Xml;

using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder;
using Microsoft.Practices.EnterpriseLibrary.Common.Instrumentation;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation;
using Microsoft.Practices.EnterpriseLibrary.Data.Properties;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;


namespace RS.DAL.Wrapper
{
    public class DalHelper
    {
      
        public DalHelper()
        {
        }

        /// <summary>
        /// Excecute dataset return the datase object
        /// </summary>
        /// <param name="strSPName">Stored Procedure Name</param>
        /// <param name="arParams">Parramer collection objects</param>
        /// <param name="conType">Type of database connect</param>
        /// <returns></returns>
        public  DataSet  dbExecuteDataset(string strSPName , object[,] arParams,string conType) 
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(conType);
                SqlDatabase db = new SqlDatabase(ConnectionString);
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                {
                    db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                }

                return db.ExecuteDataSet(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
          }

        public DataSet dbExecuteDatasetWithTableType(string strSPName, object[,] arParams, string conType)
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(conType);
                SqlDatabase db = new SqlDatabase(ConnectionString);
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));
                for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                {
                    db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (SqlDbType)Enum.Parse(typeof(SqlDbType), arParams[i, 1].ToString()), arParams[i, 2]);
                }

                return db.ExecuteDataSet(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        ////-- working on it
        ///// <summary>
        ///// Excecute dataset return the datase object
        ///// </summary>
        ///// <param name="strSPName">Stored Procedure Name</param>
        ///// <param name="arParams">Parramer collection objects</param>
        ///// <param name="conType">Type of database connect</param>
        ///// <returns></returns>
        //public DataSet dbExecuteDataset(string strSPName, object[,] arParams, string conType,bool outs)
        //{
        //   string ConnectionString = string.Empty;
        //     ConnectionString = GetConnectionString(conType);
        //     SqlDatabase db = new SqlDatabase(ConnectionString);
        //     DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
        //    for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
        //    {
              
        //        db.AddParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()),ParameterDirection.Input, String.Empty, DataRowVersion.Default, arParams[i, 2]);
        //    }

        //    return db.ExecuteDataSet(dbCommand);
        //}


        /// <summary>
        /// dbExecuteDataset return dataset object
        /// </summary>
        /// <param name="strSPName">stored procedrue name</param>
        /// <param name="conType">Type of the database</param>
        /// <returns></returns>
         public DataSet  dbExecuteDataset(string strSPName , string conType) 
          {
              try
              {
                  string ConnectionString = string.Empty;
                  ConnectionString = GetConnectionString(conType);
                  SqlDatabase db = new SqlDatabase(ConnectionString);
                  DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                  dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                  return db.ExecuteDataSet(dbCommand);
              }
              catch (Exception ex)
              {
                  throw ex;
              }
          }

        /// <summary>
        /// dbExecuteNonQuery returns bool value if there are records affected
        /// </summary>
        /// <param name="strSPName">Stored Procedure Name</param>
        /// <param name="arParams">Paramer collection</param>
        /// <param name="conType">Type of the database</param>
        /// <returns></returns>
         public bool dbExecuteNonQuery(string strSPName, object[,] arParams, string conType)
         {
             try
             {

                 string ConnectionString = string.Empty;
                 ConnectionString = GetConnectionString(conType);
                 SqlDatabase db = new SqlDatabase(ConnectionString);
                 DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                 dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                 
                 for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                 {
                     db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                 }

                 if (db.ExecuteNonQuery(dbCommand) >= 1)
                     return true;
                 else
                     return false;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         }

                 

        /// <summary>
         /// dbExecuteNonQuery returns boolian value ; if record affected or not
        /// </summary>
        /// <param name="strSPName">stored procedure name</param>
        /// <param name="conType"></param>
        /// <returns></returns>
         public bool dbExecuteNonQuery(string strSPName , string conType)
         {
             try
             {
                 string ConnectionString = string.Empty;
                 ConnectionString = GetConnectionString(conType);
                 SqlDatabase db = new SqlDatabase(ConnectionString);
                 DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                 dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                 if (db.ExecuteNonQuery(dbCommand) >= 1)
                     return true;
                 else
                     return false;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         }
 
       
         /// <summary>
         /// dbExecuteNonQueryInOutParams used to when need out param used
         /// </summary>
         /// <param name="strSPName"></param>
         /// <param name="arParams"></param>
         /// <param name="conType"></param>
         /// <returns></returns>
         public StringBuilder dbExecuteNonQueryInOutParams(string strSPName, object[,] arParams, string conType)
         {
             try
             {
                 string ConnectionString = string.Empty;
                 ConnectionString = GetConnectionString(conType);
                 SqlDatabase db = new SqlDatabase(ConnectionString);
                 DbCommand dbCommand = db.GetStoredProcCommand(strSPName);

                 dbCommand.CommandTimeout =Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
  


                 for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                 {

                     if (arParams[i, 3].ToString() == GeneralConstants.DIRECTION_OUTPUT_PARAMETER)
                     {
                         db.AddOutParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), Convert.ToInt32(arParams[i, 2]));
                     }
                     else
                     {
                         db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                     }
                 }

                 int statusCount = db.ExecuteNonQuery(dbCommand);

                 StringBuilder stringBuilder = new StringBuilder();

                 for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                 {
                     if (i != 0)
                         stringBuilder.Append(GeneralConstants.STRING_COLON_SEPARATOR);
                     stringBuilder.Append(db.GetParameterValue(dbCommand, arParams[i, 0].ToString()));
                 }
                 return stringBuilder;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         }



        /// <summary>
        ///  it will be return the out put parameter also if its contain with the input param
        /// </summary>
        /// <param name="strSPName">stored procedure name</param>
        /// 
        /// <param name="arParams">Parameter List 
        /// Example : 
        /// object[,] arParams = new object[2, 4];
        /// arParams[0, 0] = "cFieldName";  // field Name
        /// arParams[0, 1] = DbType.String; // field type
        /// arParams[0, 2] = 6; // size of the Field
        /// arParams[0, 3] = GeneralConstants.DIRECTION_OUTPUT_PARAMETER; // Parameter Direction
        /// 
        /// arParams[1, 0] = "cLocationID";
        /// arParams[1, 1] = DbType.String;
        /// arParams[1, 2] = docketsDTO.LocationID.Trim();
        /// arParams[1, 3] = GeneralConstants.DIRECTION_INPUT_PARAMETER
        /// </param>
        /// <param name="db">SQL Database object handle transaction</param>
        /// <param name="transaction">transaction Object</param>
        /// <returns></returns>
        public StringBuilder dbExecuteNonQueryInOutParameter(string strSPName, object[,] arParams, SqlDatabase db, DbTransaction transaction)
         {

             try
             {
                 DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                 dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                 for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                 {
                     if (arParams[i, 3].ToString() == GeneralConstants.DIRECTION_OUTPUT_PARAMETER)
                     {
                         db.AddOutParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), Convert.ToInt32(arParams[i, 2]));
                     }
                     else
                     {
                         db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                     }
                 }

                 int statusCount = db.ExecuteNonQuery(dbCommand, transaction);
                 StringBuilder stringBuilder = new StringBuilder();

                 for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                 {
                     if (arParams[i, 3].ToString() == GeneralConstants.DIRECTION_OUTPUT_PARAMETER)
                     {
                         if (i != 0)
                             stringBuilder.Append(GeneralConstants.STRING_COLON_SEPARATOR);

                         stringBuilder.Append(db.GetParameterValue(dbCommand, arParams[i, 0].ToString()));
                     }
                 }
                 return stringBuilder;
             }
             catch (Exception ex)
             {
                 throw ex;
             }
         }

        /// <summary>
        /// Get the connection string from the WebConfig
        /// </summary>
        /// <param name="conType"></param>
        /// <returns></returns>
        /// #TODO : have to remove the hardcode value
        private string GetConnectionString(string conType) 
        {
            try
            {
                return SecurityUtility.DecryptString(ConfigurationSettings.AppSettings[GeneralConstants.DB_CONNECTION_STRING]);
              
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get the SQL Database Objects
        /// </summary>
        /// <param name="conType"></param>
        /// <returns></returns>
        public SqlDatabase GetDatabase(string countryCode)
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(countryCode);
                return new SqlDatabase(ConnectionString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Implements transaction
        /// </summary>
        /// <param name="strSPName"></param>
        /// <param name="arParams"></param>
        /// <param name="db"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool dbExecuteNonQuery(string strSPName, object[,] arParams, SqlDatabase db, DbTransaction transaction)
        {
            try
            {
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));
               
                for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                {
                    db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                }
                if (db.ExecuteNonQuery(dbCommand, transaction) >= 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Execute dataset return the database object
        /// </summary>
        /// <param name="strSPName">Stored Procedure Name</param>
        /// <param name="arParams">Parameter collection objects</param>
        /// <param name="conType">Type of database connect</param>
        /// <returns></returns>
        public int  dbExecuteScalar(string strSPName, object[,] arParams, string conType)
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(conType);
                SqlDatabase db = new SqlDatabase(ConnectionString);
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                {
                    db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                }
                return (int)db.ExecuteScalar(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// ExEcute reader returns the integer value 
        /// </summary>
        /// <param name="strSPName">stored procedure name</param>
        /// <param name="conType">Connection type</param>
        /// <returns></returns>
        public int dbExecuteScalar(string strSPName,  string conType)
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(conType);
                SqlDatabase db = new SqlDatabase(ConnectionString);
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                return (int)db.ExecuteScalar(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Excute redear returns the integer value 
        /// </summary>
        /// <param name="strSPName">stored proceudre name</param>
        /// <param name="conType">Connection time</param>
        /// <returns></returns>
        public SqlDataReader dbExecuteReader(string strSPName, string conType)
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(conType);
                SqlDatabase db = new SqlDatabase(ConnectionString);
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                return (SqlDataReader)db.ExecuteReader(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Execute reader accepts parameters and returns
        /// </summary>
        /// <param name="strSPName">stored procedure name</param>
        /// <param name="arParams">list of parameters</param>
        /// <param name="conType">connection type</param>
        /// <returns></returns>
        public SqlDataReader dbExecuteReader(string strSPName, object[,] arParams, string conType)
        {
            try
            {
                string ConnectionString = string.Empty;
                ConnectionString = GetConnectionString(conType);
                SqlDatabase db = new SqlDatabase(ConnectionString);
                DbCommand dbCommand = db.GetStoredProcCommand(strSPName);
                dbCommand.CommandTimeout = Convert.ToInt16((ConfigurationSettings.AppSettings["commandTimeout"]));  
                for (int i = arParams.GetLowerBound(0); i <= arParams.GetUpperBound(0); i++)
                {
                    db.AddInParameter(dbCommand, arParams[i, 0].ToString(), (DbType)Enum.Parse(typeof(DbType), arParams[i, 1].ToString()), arParams[i, 2]);
                }
                return (SqlDataReader)db.ExecuteScalar(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}